package Server.DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class SchoolClassQuery extends Query {
	/**
	 * creat Class object from sql by idClass 
	 */
	public SchoolClassQuery(){
			super();
		}
		 /**
		  *  check if class is exits 
		  * @param idClass
		  * @return   boolean 
		  * @throws SQLException
		  */
		public boolean isExist(int idClass) throws SQLException{
			setQuery("SELECT * FROM class " + "WHERE idClass = ?");
			PreparedStatement query = getPS();
			query.setInt(1, idClass);
			if (query.executeQuery().next()){
				query.close();
				return true;
			}
			else {
				query.close();
				return false;
			}
		}
		/**
		 * 
		 * @param className
		 * @return
		 * @throws SQLException
		 */
		public boolean isExist(String className) throws SQLException{
			setQuery("SELECT * FROM class " + "WHERE className = ?");
			PreparedStatement query = getPS();
			query.setString(1, className);
			if (query.executeQuery().next()){
				query.close();
				return true;
			}
			else {
				query.close();
				return false;
			}
		}
		/**
		 * 
		 * @param idClass
		 * @return class 
		 * @throws SQLException
		 */
		public SchoolClass getClassById(int idClass) throws SQLException{
			setQuery("SELECT * FROM class WHERE idClass = '"+idClass+"'");
			ResultSet rs = execQuery();
			if (rs.next()) return new SchoolClass(rs.getInt(1), rs.getString(2), rs.getInt(3));
			else return null;
		}
		
		public SchoolClass getClassByName(String name) throws SQLException{
			setQuery("SELECT * FROM class WHERE className = '"+name+"'");
			ResultSet rs = execQuery();
			if (rs.next()) return new SchoolClass(rs.getInt(1), rs.getString(2), rs.getInt(3));
			else return null;
		}
		/**
		 * add a new class
		 * @param className
		 * @return boolean 
		 *         
		 * @throws SQLException
		 */
		public int addSchoolClass(String className) throws SQLException{
			Info info = new Info();
			int newIdClass = info.getNewClassId();
			if (isExist(className)) return 1;
			setQuery("INSERT INTO class(idClass, className, size) VALUES(?,?,?)");
			PreparedStatement query = getPS();
			query.setInt(1, newIdClass);
			query.setString(2, className);
			query.setInt(3, 0);
			query.executeUpdate();
			query.close();
			info.incClassId();
			info.close();
			return 0;
		}
		/**
		 * 
		 * @return array of classes in school
		 * @throws SQLException
		 */
		public ArrayList<SchoolClass> getAllClasses() throws SQLException{
			ArrayList<SchoolClass> array = new ArrayList<SchoolClass>();
			setQuery("SELECT * FROM class");
			ResultSet rs = execQuery();
			while (rs.next()){
				SchoolClass sClass = new SchoolClass(rs.getInt(1), rs.getString(2), rs.getInt(3));
				array.add(sClass);
			}
			return array;
		}
		
		/**
		 * 
		 * @return array of classes in school
		 * @throws SQLException
		 */
		public ArrayList<ReportIF> getClasses() throws SQLException{
			ArrayList<ReportIF> array = new ArrayList<ReportIF>();
			setQuery("SELECT * FROM class");
			ResultSet rs = execQuery();
			while (rs.next()){
				SchoolClass sClass = new SchoolClass(rs.getInt(1), rs.getString(2), rs.getInt(3));
				array.add(sClass);
			}
			return array;
		}
		/**
		 * 
		 * @param idClass
		 * @return size
		 * @throws SQLException
		 */
		public int getClassSize(int idClass) throws SQLException{
			setQuery("SELECT * FROM class WHERE idclass='"+idClass+"'");
			ResultSet rs = execQuery();
			if (rs.next()) return rs.getInt(3);
			return 0;
		}
		/**
		 * student is removed from class
		 * @param idClass
		 * @throws SQLException
		 */
		public void decClassSize(int idClass) throws SQLException{
			int size = getClassSize(idClass) -1;
			setQuery("UPDATE class SET size=? WHERE idClass=?");
			PreparedStatement query = getPS();
			query.setInt(1, size);
			query.setInt(2, idClass);
			query.executeUpdate();
			query.close();
		}
		/**
		 * student assign to class
		 * @param studentsArray
		 * @param idClass
		 * @throws SQLException
		 */
		public void assignStudents(ArrayList<Student> studentsArray, int idClass) throws SQLException{
			int size = getClassSize(idClass) + studentsArray.size();
			setQuery("UPDATE class SET size=? WHERE idClass=?");
			PreparedStatement query = getPS();
			query.setInt(1, size);
			query.setInt(2, idClass);
			query.executeUpdate();
			query.close();
			for (int i=0; i<studentsArray.size(); i++)
				new StudentQuery().assginStudent(studentsArray.get(i).getIdUser(), idClass);
		}
		/**
		 * student removed from class
		 * @param studentsArray
		 * @throws SQLException
		 */
		public void unAssignStudents(ArrayList<Student> studentsArray) throws SQLException{
			for (int i=0; i<studentsArray.size(); i++)
				decClassSize(new StudentQuery().unAssginStudent(studentsArray.get(i).getIdUser()));
		}
		/**
		 * 
		 * @param idClass
		 * @return all student in class
		 * @throws SQLException
		 */
		public ArrayList<Student> getStudentsInClass(int idClass) throws SQLException{
			ArrayList<Student> array = new ArrayList<Student>();
			setQuery("SELECT * " +
					"FROM user, student " +
					"WHERE user.iduser=student.iduser and student.idClass='" + idClass +
					"' GROUP BY user.lastName, user.firstName");
			ResultSet rs = execQuery();
			while (rs.next()){
				Student student = new Student(rs.getInt(1), rs.getString(2), rs.getString(3), rs.getInt(4),
						rs.getInt(5), rs.getString(6),rs.getString(7), rs.getInt(8));
				array.add(student);
			}
			return array;
		}
		/**
		 * 
		 * @param idLesson
		 * @param idClass
		 * @return list of all students that assign to lesson
		 * @throws SQLException
		 */
		public ArrayList<Student> signClassToLesson(int idLesson, int idClass) throws SQLException{
			ArrayList<Student> students = getStudentsInClass(idClass);
			ArrayList<Student> notPass = new ArrayList<Student>();
			for (Student student : students)
				if (!new StudentQuery().signStudentToLesson(student, idLesson))
					notPass.add(student);
			return notPass;
		}
		
		 public void removeClass(String name) throws SQLException{
	        	setQuery("DELETE FROM class WHERE className=?");
	        	PreparedStatement query = getPS();
	        	query.setString(1, name);
	        	query.executeUpdate();
	        }
		 
		 public int getId(String className) throws SQLException{
				setQuery("SELECT * FROM class WHERE className = '"+className+"'");
				ResultSet rs = execQuery();
				if (rs.next()){
					return rs.getInt(1);
				}
				return 0;
			}
}